-- 上机练习

-- 上机练习1 事务提交
begin;
insert into t_student VALUES 
(402,'张森','男','2003-01-01',18,1,2023) ;
update t_student set student_name='',age=19 where id=4002;
delete from t_student where age<18;
COMMIT;
rollback;

-- 上机练习2 事务提交
create DATABASE test;
use test ;
CREATE table acount (
id int auto_increment primary key,
accont_name varchar(30) not null,
phone varchar(30) not null,
balance decimal(30,5)
)
insert into acount values 
('70263055','张三','13541522563',100),
('70263056','李四','13012123443',100);
begin;
UPDATE acount set balance=balance-50 where id=70263055;
UPDATE acount set balance=balance+50 where id=70263056;
COMMIT;
rollback;

-- 上机练习3 创建索引
CREATE TABLE student1(
id int auto_increment PRIMARY KEY,
student_name VARCHAR(20) NOT NULL,
phone VARCHAR(11) NOT NULL,
gender ENUM('男','女') DEFAULT('男'),
INDEX(id)
);
CREATE TABLE student2(
id int,
student_name VARCHAR(20) NOT NULL,
phone VARCHAR(11) NOT NULL,
gender ENUM('男','女') DEFAULT('男')
);
DELIMITER//
CREATE PROCEDURE insert_100_rows()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i<=100 DO
   INSERT student1 VALUES(i,CONCAT('张三',i),CONCAT('133',i),'男');
	 INSERT student2 VALUES(i,CONCAT('张三',i),CONCAT('133',i),'男');
	 SET i=i+1;
END WHILE;
END//
DELIMITER;
CALL insert_100_rows()
SELECT * FROM student1 WHERE id=100;
SELECT * FROM student2 WHERE id=100;
-- 上机练习4 创建视图
CREATE VIEW view_student_san
AS SELECT ID,student_name FROM t_student WHERE student_name='张耀仁';